package cn.iocoder.springboot.lab13.jpa.service;

import cn.iocoder.springboot.lab13.jpa.repository.NativeQueryRepository;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;

@Service
public class NativeQueryService {

    @PersistenceContext
    private EntityManager em;


    public List<Map<String, String>> query(){
        String sql = "SELECT  entry.ID as ID, \n" +
                " entry.FLOWID as FLOWID, \n" +
                " entry.FLOWNAME as FLOWNAME, \n" +
                " entry.FLOWTYPE as FLOWTYPE, \n" +
                " entry.INSTTYPE as INSTTYPE, \n" +
                " entry.CREATOR as CREATOR, \n" +
                " entry.CREATORNAME as CREATORNAME, \n" +
                " entry.STARTDATE as STARTDATE, \n" +
                " entry.FINISHDATE as FINISHDATE, \n" +
                " entry.ALARMDATE as ALARMDATE, \n" +
                " entry.DUEDATE as DUEDATE, \n" +
                " entry.STATUS as STATUS, \n" +
                " entry.STEPSN as STEPSN, \n" +
                " entry.REL_ID as REL_ID, \n" +
                " entry.TENANTID as TENANTID, \n" +
                " entry.ATTRS as ATTRS, \n" +
                " ids.ID as PROCESSED_ID, \n" +
                " ids.INSTID as PROCESSED_INSTID, \n" +
                " ids.ACCOUNT as PROCESSED_ACCOUNT, \n" +
                " ids.NAME as PROCESSED_NAME, \n" +
                " ids.FINISHDATE as PROCESSED_FINISHDATE , \n" +
                " list_.ID as LISTID, \n" +
                " list_.TITLE as TITLE, \n" +
                " list_.BELONGED_ORG_ID as BELONGED_ORG_ID, \n" +
                " list_.BEAN_ID as BEAN_ID, \n" +
                " list_.FORM_PREFIX as FORM_PREFIX, \n" +
                " list_.FORM_ID as FORM_ID, \n" +
                " list_.APPCAT as APPCAT, \n" +
                " list_.APPID as APPID, \n" +
                " list_.RESERVESTRING1 as RESERVESTRING1, \n" +
                " list_.RESERVESTRING2 as RESERVESTRING2, \n" +
                " list_.RESERVESTRING3 as RESERVESTRING3, \n" +
                " list_.RESERVESTRING4 as RESERVESTRING4, \n" +
                " list_.RESERVESTRING5 as RESERVESTRING5, \n" +
                " list_.RESERVELONG1 as RESERVELONG1, \n" +
                " list_.RESERVELONG2 as RESERVELONG2, \n" +
                " list_.RESERVELONG3 as RESERVELONG3, \n" +
                " list_.RESERVELONG4 as RESERVELONG4, \n" +
                " list_.RESERVELONG5 as RESERVELONG5 \n" +
                " ,oaDocDispatch.ID as ENTITYID\n" +
                " ,oaDocDispatch.BUSINESS_TYPE_NAME as BUSINESS_TYPE_NAME\n" +
                " ,oaDocDispatch.URGENCY_LEVEL as URGENCY_LEVEL\n" +
                " ,oaDocDispatch.CREATOR_NAME as CREATOR_NAME\n" +
                " ,oaDocDispatch.CREATE_DEPT_NAME as CREATE_DEPT_NAME\n" +
                " ,oaDocDispatch.CREATION_TIME as CREATION_TIME\n" +
                "\n" +
                "FROM \n" +
                "HD_WF_INST entry, HD_WF_LIST list_, HD_WF_PROCESSED ids\n" +
                ",OA_DOC_DISPATCH oaDocDispatch\n" +
                "WHERE entry.ID = ids.INSTID AND list_.ID = ids.INSTID AND list_.BEAN_ID = oaDocDispatch.ID   ORDER BY entry.STARTDATE DESC";
        Query nativeQuery = em.createNativeQuery(sql);
        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
       return nativeQuery.getResultList();
    }
}
